import datetime
import os.path
import re
import webbrowser

import pandas as pd
from pyecharts.charts import Page
from pyecharts.components import Table
from pyecharts.options import ComponentTitleOpts

import common
from common import index


class Test:
    def __int__(self):
        pass

    # def Test(self,item,dff):
    #     for i in dff['供应商名称']:
    #         if item in i:
    #             return i
    #     return item

    def test(self,item, dff):
        matching_supplier = next((i for i in dff['供应商名称'] if item in i), item)
        return matching_supplier
    def get2003(self, filename):
        df = pd.read_excel(filename, sheet_name='Sheet1', usecols=['序号', '供应商名称', '采购类别'])
        return df

    def getfilepaths(self, filename):
        filepaths = index.get_file_path(filename)
        if len(filepaths) == 0:
            return None
        else:
            return filepaths

    def getData(self, zd, dfaaa):
        filepaths = self.getfilepaths(zd['按期交货率'])
        dfs = pd.DataFrame()
        if filepaths:
            for path in filepaths:
                # 获取这个工作薄中所有的sheet名
                sheet_names = pd.ExcelFile(path).sheet_names

                for sheet_name in sheet_names:
                    if sheet_name.endswith('月'):
                        # 读取这个sheet中的所有内容
                        df = pd.read_excel(path, sheet_name=sheet_name, skiprows=1,
                                           usecols=['供方名称', '按时完成订单数', '本月应完成订单数'])
                        # 删除'供方'是NaN的行
                        df = df.dropna(subset=['供方名称'])
                        df.fillna(0, inplace=True)

                        # 删除'本月应完成订单数','按时完成订单数'都为0的行
                        df=df[(df[['本月应完成订单数','按时完成订单数']] !=0).any(axis=1)]
                        df['月份'] = sheet_name.replace('月', '')
                        dfs = pd.concat([dfs, df])
            dfs.reset_index(inplace=True)
            dfm = dfs[dfs['月份'].isin(zd['所选月份'])]
            # df['供方名称'] = [item for item in df['供方名称'].map(lambda x: self.Test(x, dfaaa))]
            df['供方名称'] = df['供方名称'].apply(lambda x: self.test(x, dfaaa))
            dfm = dfm.pivot_table(index='供方名称', values=['本月应完成订单数', '按时完成订单数'], aggfunc='sum')
            dfm.reset_index(inplace=True)

            dfm['按期交付率'] = dfm['按时完成订单数'].div(dfm['本月应完成订单数']).map(lambda x: '{:.2%}'.format(x))
            return dfm

    def getData2(self, zd, dfaaa):
        filepaths = self.getfilepaths(zd['月份一次交检合格率'])
        dfs = pd.DataFrame()
        if filepaths:
            for path in filepaths:
                # 获取这个工作薄中所有的sheet名
                sheet_names = pd.ExcelFile(path).sheet_names

                for sheet_name in sheet_names:
                    if (sheet_name == '外购物资') | (sheet_name == '外协件'):
                        # 读取这个sheet中的所有内容
                        df = pd.read_excel(path, sheet_name=sheet_name, skiprows=1,
                                           usecols=['供应商', '合格数量/批次', '送检数量/批次'])
                        # 删除'供方'是NaN的行
                        df = df.dropna(subset=['供应商'])
                        df.fillna(0, inplace=True)
                        # 删除'本月应完成订单数','按时完成订单数'都为0的行
                        df=df[(df[['合格数量/批次','送检数量/批次']] !=0).any(axis=1)]
                        df['月份'] = re.search(r'(\d+)月', path).group(1)
                        dfs = pd.concat([dfs, df])
            dfs.reset_index(inplace=True)
            dfm = dfs[dfs['月份'].isin(zd['所选月份'])]
            # dfm['供应商'] = [item for item in dfm['供应商'].map(lambda x: self.Test(x, dfaaa))]
            dfm['供应商'] = dfm['供应商'].apply(lambda x: self.test(x, dfaaa))
            dfm = dfm.pivot_table(index='供应商', values=['合格数量/批次', '送检数量/批次'], aggfunc='sum')
            dfm.reset_index(inplace=True)

            dfm['一次交检合格率'] = dfm['合格数量/批次'].div(dfm['送检数量/批次']).map(lambda x: '{:.2%}'.format(x))
            return dfm

    def getData3(self, zd):
        df = self.get2003(zd['目标文件'])
        dfs = self.getData(zd, df)
        aa = df.merge(dfs, left_on='供应商名称', right_on='供方名称', how='left')
        dfs2 = self.getData2(zd, df)
        aa = aa.merge(dfs2, left_on='供应商名称', right_on='供应商', how='left')
        aa = aa[['序号', '供应商名称', '采购类别', '按期交付率', '一次交检合格率']]
        aa.fillna('', inplace=True)
        result_folder = os.path.join(common.base_path, '结果')
        excel_folder = os.path.join(result_folder, 'Excel')
        html_folder = os.path.join(result_folder, 'Html')
        save_path = [excel_folder, html_folder]
        index.create_folder_if_not_exists(save_path)

        # 将整个DataFrame写入Excel表格
        with pd.ExcelWriter(os.path.join(save_path[0], f"{"-".join(zd['所选月份'])}.xlsx"),
                            engine='xlsxwriter') as writer: aa.to_excel(writer, sheet_name='Sheet1', index=False)

        # 写入html表格并打开
        page = Page(layout=Page.SimplePageLayout)  # 网页中各子图可拖动。默认的SimplePageLayout不可拖动
        table1 = Table()
        if aa.shape[0] > 0:
            table1.add(aa.columns.tolist(), aa.values.tolist())
            table1.set_global_opts(title_opts=ComponentTitleOpts(title=f"{"-".join(zd['所选月份'])} 月份统计结果",
                                                                 subtitle=(datetime.datetime.now().strftime(
                                                                     '%Y-%m-%d %H:%M:%S'))))

        page.add(table1)
        page.render(os.path.join(save_path[1], f"{"-".join(zd['所选月份'])}.html"))
        webbrowser.open(os.path.join(save_path[1], f"{"-".join(zd['所选月份'])}.html"))
